﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LevenBlog.Data.SqlServer
{
    public static class PageHelper
    {
        #region 生成分页Sql语句

        /// <summary>
        /// 构造分页SQL语句
        /// </summary>
        /// <param name="pageSize">分页大小</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="fldName">查询的列名，默认为*</param>
        /// <param name="tableName">查询的表名，不能为空</param>
        /// <param name="strWhere">查询条件，不加where</param>
        /// <param name="keyId">查询主键，默认为ID</param>
        /// <param name="order">排序语句，不带Order By</param>
        /// <returns></returns>
        public static string BuildSQL(int pageSize, int currentPage, string fldName, string tableName, string strWhere, string keyId, string order)
        {
            string SQL = string.Empty;
            string whereStr1 = string.Empty;
            string whereStr2 = string.Empty;
            string orderStr = string.Empty;
            string filedName = "*";
            string key = "ID";
            if (!string.IsNullOrEmpty(strWhere))
            {
                whereStr1 = " Where " + strWhere;
                whereStr2 = " AND " + strWhere;
            }

            if (!string.IsNullOrEmpty(key))
            {
                key = keyId;
            }
            if (!string.IsNullOrEmpty(order))
            {
                //orderStr = " Order By " + key +" "+order;
                orderStr = " Order By " + order;
            }
            if (!string.IsNullOrEmpty(fldName))
            {
                filedName = fldName;
            }

            if (currentPage <= 1)
            {
                SQL = string.Format("Select TOP {0} {1} From {2}{3}{4}", pageSize, filedName, tableName, whereStr1, orderStr);
            }
            else
            {
                SQL = string.Format("Select TOP {0} {1} From {2} Where ({3} NOT IN (Select TOP {4} {3} From {2} {5}{7})){6}{7}"
                        , pageSize, filedName, tableName, key, pageSize * (currentPage - 1), whereStr1, whereStr2, orderStr);
            }
            return SQL;
        }

        /// <summary>
        /// 生成分页Sql语句
        /// </summary>
        /// <param name="pageSize">分页大小</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="fldName">字段列表</param>
        /// <param name="tableName">数据库表名</param>
        /// <param name="strWhere">分页查询条件,不要加Where</param>
        /// <param name="KeyID">数据库中自动编号的ID的值</param>
        /// <param name="OrderType">0为按升序,1为按降序</param>
        /// <returns>返回用来查询分页的sql语句</returns>
        public static string BuildPageSql(int pageSize, int currentPage, string fldName, string tableName, string strWhere, string KeyID, string order)
        {
            string strSql = null;
            string strWhere2 = "";
            string strAnd = "";
            string strOrder = "";
            if (strWhere != string.Empty)
            {
                strWhere2 = " Where " + strWhere;
                strAnd = " And ";
            }
            if (KeyID == string.Empty)
                KeyID = "[ID]";
            if (fldName == string.Empty)
                fldName = "*";
            strOrder = order;
            if (currentPage <= 1)
            {
                strSql = "Select Top " + pageSize.ToString() + " " + fldName + " From " + tableName + " " + strWhere2 + strOrder;
            }
            else
            {
                strSql = "Select Top " + pageSize.ToString() + " " + fldName + " From " + tableName +
                 " Where (" + KeyID + " Not in (Select Top " + (pageSize * (currentPage - 1)).ToString() +
                 " " + KeyID + " From " + tableName + strWhere2 + strOrder + "))" + strAnd + strWhere + strOrder;
            }
            return strSql;
        }

        /// <summary>
        /// 生成分页Sql语句,按降序排列
        /// </summary>
        /// <param name="pageSize">分页大小</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="tableName">数据库表名</param>
        /// <returns>返回用来查询分页的sql语句</returns>
        public static string BuildPageSql(int pageSize, int currentPage, string tableName)
        {
            return BuildPageSql(pageSize, currentPage, "", tableName, "", "", "");
        }
        /// <summary>
        /// 生成分页Sql语句
        /// </summary>
        /// <param name="pageSize">分页大小</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="tableName">数据库表名</param>
        /// <param name="OrderType">0为按升序,1为按降序</param>
        /// <returns>返回用来查询分页的sql语句</returns>
        public static string BuildPageSql(int pageSize, int currentPage, string tableName, string order)
        {
            return BuildPageSql(pageSize, currentPage, "", tableName, "", "", order);
        }
        /// <summary>
        /// 生成分页Sql语句
        /// </summary>
        /// <param name="pageSize">分页大小</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="tableName">数据库表名</param>
        /// <param name="strWhere">分页查询条件,不要加Where</param>
        /// <param name="OrderType">0为按升序,1为按降序</param>
        /// <returns>返回用来查询分页的sql语句</returns>
        public static string BuildPageSql(int pageSize, int currentPage, string tableName, string strWhere, string order)
        {
            return BuildPageSql(pageSize, currentPage, "", tableName, strWhere, "", order);
        }
        /// <summary>
        /// 生成分页Sql语句
        /// </summary>
        /// <param name="pageSize">分页大小</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="tableName">数据库表名</param>
        /// <param name="strWhere">分页查询条件,不要加Where</param>
        /// <param name="KeyID">数据库中自动编号的ID的值</param>
        /// <param name="OrderType">0为按升序,1为按降序</param>
        /// <returns>返回用来查询分页的sql语句</returns>
        public static string BuildPageSql(int pageSize, int currentPage, string tableName, string strWhere, string KeyID, string order)
        {
            return BuildPageSql(pageSize, currentPage, "", tableName, strWhere, KeyID, order);
        }
        #endregion
    }
}
